import json
import pymysql

conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='oldpan',
    passwd='whnb',
    database='tpp',
    charset='utf8',
)
cur = conn.cursor()

with open('cities.json', mode='r', encoding='utf-8') as f:
    data = json.loads(f.read())
    # print(data)
    returnValue = data['returnValue']
    for letter, cities in returnValue.items():
        # sql_letter = f"insert into tb_letter (letter) values ('{letter}');"
        # cur.execute(sql_letter)
        # conn.commit()
        cur.execute(f"select id from tb_letter where letter = '{letter}';")
        letter_id = cur.fetchone()[0]
        for city in cities:
            cid = city['id']
            parentId = city['parentId']
            regionName = city['regionName']
            cityCode = city['cityCode']
            pinYin = city['pinYin']
            sql_city = f"""insert into tb_city (city_id, parent_id, region_name, city_code, pinyin, letter_id)
values ({cid}, {parentId}, '{regionName}', {cityCode}, '{pinYin}', {letter_id});"""
            cur.execute(sql_city)
            conn.commit()
            # print(sql_city)

cur.close()
conn.close()